package com.ssy.lingxi.member.merchant.utils;

import com.ssy.lingxi.member.merchant.config.ServiceConfig;
import com.ssy.lingxi.member.merchant.model.bo.ExcelFileContentBO;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * 读写Excel文件类
 * @author 万宁
 * @version 2.0.0
 * @date 2020-07-13
 */
public class ExcelFileUtil {

//    public static void main(String[] args) throws IOException {
//        String path = "d:/member_template.xlsx";
//        ExcelFileContentBO bo = readMemberImportUploadFile(path);
//        System.out.println(bo.toString());
//    }

    public static void writeMemberImportTemplate(String filePath, List<String> headers) throws IOException {
        FileOutputStream fileOutputStream = new FileOutputStream(new File(filePath),false);
        XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
        XSSFSheet sheet = xssfWorkbook.createSheet();
        // 创建一新行
        XSSFRow row = sheet.createRow(0);

        for(int i =0 ; i<headers.size(); i++) {
            sheet.setColumnWidth(i, 4000);
            XSSFCell cell = row.createCell(i);
            cell.setCellValue(headers.get(i));
        }

        xssfWorkbook.write(fileOutputStream);
        fileOutputStream.close();
        xssfWorkbook.close();
    }

    public static ExcelFileContentBO readMemberImportUploadFile(String filePath) throws IOException {
        ExcelFileContentBO contentBO = new ExcelFileContentBO();

        FileInputStream fStream = new FileInputStream(filePath);
        XSSFWorkbook workbook = new XSSFWorkbook(fStream);
        XSSFSheet sheet = workbook.getSheetAt(0);
        if(sheet == null) {
            return contentBO;
        }

        //要使用DataFormatter来读取Cell的数据，否则报错
        DataFormatter formatter = new DataFormatter();

        int rowNum = 0;
        while (true) {
            XSSFRow row = sheet.getRow(rowNum);
            if(row == null) {
                break;
            }

            int colNum = 0;
            while (true) {
                XSSFCell cell = row.getCell(colNum);
                if (cell == null) {
                    break;
                }

                ExcelFileContentBO.CellItem item = new ExcelFileContentBO.CellItem();
                item.setRowNo(rowNum);
                item.setFieldNo(colNum);
                item.setFieldValue(formatter.formatCellValue(cell));
                if (rowNum == 0) {
                    item.setFieldValue(item.getFieldValue().replace(ServiceConfig.CHANGE_EMAIL_SMS_CODE_REDIS_KEY_PREFIX,""));
                    contentBO.getHeaders().add(item);
                } else {
                    contentBO.getContents().add(item);
                }
                colNum++;
            }

            rowNum ++;
        }

        contentBO.setRowCount(rowNum);
        contentBO.setFieldCount(contentBO.getHeaders().size());

        return contentBO;
    }

//    public static ExcelFileContentBO readMemberImportUploadFile(String filePath) throws IOException {
//        ExcelFileContentBO contentBO = new ExcelFileContentBO();
//
//        FileInputStream fStream = new FileInputStream(filePath);
//        XSSFWorkbook workbook = new XSSFWorkbook(fStream);
//        XSSFSheet sheet = workbook.getSheetAt(0);
//        if(sheet == null) {
//            return new ExcelFileContentBO();
//        }
//
//        //解析头行
//        List<ExcelFileContentBO.CellItem> headers = new ArrayList<>();
//        XSSFRow headerRow = sheet.getRow(0);
//        if(headerRow == null) {
//            return new ExcelFileContentBO();
//        }
//
//        int col = 1;
//        for (Cell cell: headerRow) {
//            ExcelFileContentBO.CellItem item = new ExcelFileContentBO.CellItem();
//            item.setRowNo(0);
//            item.setFieldNo(col);
//            item.setFieldValue(cell.getStringCellValue());
//            headers.add(item);
//            col++;
//        }
//        contentBO.setHeaders(headers);
//        contentBO.setFieldCount(headers.size());
//
//        List<ExcelFileContentBO.CellItem> rows = new ArrayList<>();
//        int rowNo = 0;
//        for(Row row : sheet) {
//            //遇到空行停止
//            if (row==null) {
//                break;
//            }
//
//            //跳过头行
//            if (rowNo == 0) {
//                rowNo++;
//                continue;
//            }
//
//            col = 1;
//            for (Cell cell : row) {
//                DataFormatter formatter = new DataFormatter();
//                formatter.f
//                ExcelFileContentBO.CellItem item = new ExcelFileContentBO.CellItem();
//                item.setRowNo(rowNo);
//                item.setFieldNo(col);
//                item.setFieldValue(cell.getStringCellValue());
//                rows.add(item);
//                col++;
//            }
//
//            rowNo++;
//        }
//
//        contentBO.setRowCount(rowNo);
//        contentBO.setContents(rows);
//
//        return contentBO;
//    }
}
